package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MLocator;
import org.compiere.model.MOrg;
import org.compiere.model.MPeriod;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

public class MM_Form_300 extends SvrProcess {
	//Form_300_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_MM_FRP_ID, p_MM_FRP_prepared_ID, p_MM_FRP_checked_ID, p_C_Period_ID;
	Date p_DatePrepare;
	/** */
	String ResultPath;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("MM_FRP_ID")) 
				p_MM_FRP_ID = para[i].getParameterAsInt();
			else if (name.equals("MM_FRP_prepared_ID")) 
				p_MM_FRP_prepared_ID = para[i].getParameterAsInt();
			else if (name.equals("MM_FRP_checked_ID")) 
				p_MM_FRP_checked_ID = para[i].getParameterAsInt();
			else if (name.equals("C_Period_ID")) 
				p_C_Period_ID = para[i].getParameterAsInt();
			else if (name.equals("DatePrepare")) 
				p_DatePrepare = (Timestamp) para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_300_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 10) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//New Cell Style
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom((short)1);
		cs.setBorderTop((short)1);
		cs.setBorderLeft((short)1);
		cs.setBorderRight((short)1);
		cs.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		cs.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		
		//Header
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(12).getCell(56).setCellValue((new MLocator(m_ctx, p_MM_FRP_ID, get_TrxName()).getValue()));
		sheet.getRow(16).getCell(28).setCellValue((new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getName()));
		sheet.getRow(18).getCell(1).setCellValue((new SimpleDateFormat("dd").format(p_DatePrepare)));
		sheet.getRow(18).getCell(4).setCellValue((new SimpleDateFormat("MMMMM").format(p_DatePrepare)));
		sheet.getRow(18).getCell(9).setCellValue((new SimpleDateFormat("yyyy").format(p_DatePrepare)));
		sheet.getRow(19).getCell(55).setCellValue((new MLocator(m_ctx, p_MM_FRP_prepared_ID, get_TrxName()).getValue()));
		sheet.getRow(20).getCell(55).setCellValue((new MLocator(m_ctx, p_MM_FRP_checked_ID, get_TrxName()).getValue()));
		
		//Positions
		int row = 25;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "WITH t1 AS ( " + 
						" SELECT 	fa.dateacct " + 
						" , iohead.documentno " + 
						" , bp.name AS bpartner " + 
						" , p.value AS ProductCode " + 
						" , p.name AS product " + 
						" , uom.name AS uom " + 
						" , ioline.movementqty " + 
						" , fa.amtacctdr " + 
						" FROM fact_acct fa " + 
						" JOIN m_inout iohead ON fa.record_id = iohead.m_inout_id " + 
						" JOIN m_inoutline ioline ON fa.record_id = ioline.m_inout_id AND fa.line_id = ioline.m_inoutline_id " + 
						" JOIN m_locator l ON l.m_locator_id = ioline.m_locator_id " + 
						" LEFT JOIN c_bpartner bp ON bp.c_bpartner_id = iohead.c_bpartner_id " + 
						" LEFT JOIN m_product p ON p.m_product_id = ioline.m_product_id " + 
						" LEFT JOIN c_uom_trl uom ON uom.c_uom_id = p.c_uom_id " + 
						" WHERE fa.ad_table_id = 319::numeric " + 
						" AND fa.counter < fa.reference " + 
						" AND iohead.docstatus <> 'VO'::bpchar " +  
						" AND iohead.docstatus <> 'RE'::bpchar " + 
						" AND fa.DateAcct BETWEEN '" + (new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getStartDate()) + "' AND '" + (new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getEndDate()) + "' " +
						" AND ioline.M_Locator_ID = " + p_MM_FRP_ID + 
						" UNION ALL " + 
						" SELECT 	fa.dateacct " + 
						" , mhead.documentno " + 
						" , l.Value AS BPartner " + 
						" , p.value AS productCode " + 
						" , p.name AS product " + 
						" , uom.name AS uom " + 
						" , mline.movementqty " + 
						" , fa.amtacctdr " + 
						" FROM fact_acct fa " + 
						" JOIN m_movement mhead ON fa.record_id = mhead.m_movement_id " + 
						" JOIN m_movementline mline ON fa.record_id = mline.m_movement_id AND fa.line_id = mline.m_movementline_id " + 
						" JOIN m_locator l ON l.m_locator_id = mline.m_locator_id " + 
						" LEFT JOIN c_bpartner bp ON bp.c_bpartner_id = mhead.c_bpartner_id " + 
						" LEFT JOIN m_product p ON p.m_product_id = mline.m_product_id " + 
						" LEFT JOIN c_uom_trl uom ON uom.c_uom_id = p.c_uom_id " + 
						" WHERE fa.ad_table_id = 323::numeric " + 
						" AND fa.counter > fa.reference " + 
						" AND mhead.docstatus <> 'VO'::bpchar " + 
						" AND mhead.docstatus <> 'RE'::bpchar " + 
						" AND fa.DateAcct BETWEEN '" + (new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getStartDate()) + "' AND '" + (new MPeriod(m_ctx, p_C_Period_ID, get_TrxName()).getEndDate()) + "' " +
						" AND mline.M_Locator_ID = " + p_MM_FRP_ID + ") " + 
						" SELECT DateAcct " + 
						" , Documentno " + 
						" , BPartner " + 
						" , ProductCode " + 
						" , Product " + 
						" , uom " + 
						" , movementqty " + 
						" , amtacctdr " + 
						" FROM t1 " + 
						" UNION ALL " + 
						" SELECT (NULL)::TIMESTAMP AS DateAcct " + 
						" , DocumentNo " + 
						" , (NULL)::CHARACTER VARYING AS BPartner " + 
						" , (NULL)::CHARACTER VARYING AS ProductCode " + 
						" , (NULL)::CHARACTER VARYING AS Product " + 
						" , (NULL)::CHARACTER VARYING AS UOM " + 
						" , (NULL)::NUMERIC AS MovementQty " + 
						" , sum(AmtAcctDr) " + 
						" FROM t1 " + 
						" GROUP BY DocumentNo " + 
						" UNION ALL " + 
						" SELECT (NULL)::TIMESTAMP AS DateAcct " +  
						" , (NULL)::CHARACTER VARYING AS DocumentNo " + 
						" , (NULL)::CHARACTER VARYING AS BPartner " + 
						" , (NULL)::CHARACTER VARYING AS ProductCode " + 
						" , (NULL)::CHARACTER VARYING AS Product " + 
						" , (NULL)::CHARACTER VARYING AS UOM " + 
						" , (NULL)::NUMERIC AS MovementQty " + 
						" , sum(AmtAcctDr) " + 
						" FROM t1 " + 
						" ORDER BY DocumentNo, DateAcct";
		
		try {
			ps = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = ps.executeQuery();
			
			while (rs.next()) {
				//shifting and merging
				sheet.shiftRows(row, row + 7, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 4));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 5, 11));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 12, 24));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 25, 31));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 44));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 45, 49));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 50, 55));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 56, 63));
				
				R = sheet.createRow(row);
				for (int i = 0; i < 64; i++) {
					C = R.createCell(i);
					C.setCellStyle(cs);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(rs.getDate("DateAcct") == null ? "Итог" : (new SimpleDateFormat("dd.MM.yyyy").format(rs.getDate("DateAcct"))));
				sheet.getRow(row).getCell(5).setCellValue(rs.getString("DocumentNo") == null ? "всего" : rs.getString("DocumentNo"));
				sheet.getRow(row).getCell(12).setCellValue(rs.getString("BPartner"));
				sheet.getRow(row).getCell(25).setCellValue(rs.getString("ProductCode"));
				sheet.getRow(row).getCell(32).setCellValue(rs.getString("Product"));
				sheet.getRow(row).getCell(45).setCellValue(rs.getString("UOM"));
				sheet.getRow(row).getCell(50).setCellValue(rs.getFloat("MovementQty"));
				sheet.getRow(row).getCell(56).setCellValue(rs.getFloat("AmtAcctDr"));
				
				row++;
			}
		} catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		} finally {
			DB.close(rs, ps);
			rs = null;
			ps = null;
		}
		
	
		//---------------------------------------------------------------
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();

		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}

}
